import pymysql
from time import sleep
import pandas as pd
from pandas import DataFrame as df
import requests
from lxml import etree
import random


class training_detail:
    host = '192.168.0.113'
    port = 3306
    user = 'coname'
    passwd = 'xZS6p4LDcKCwn4Yb'
    charset = 'utf8'
    db = 'co'

    def __init__(self):  # 初始化读取excel
        self.wb = pd.read_excel(r'D:\wky\spider\training\traininger\training.xlsm', sheet_name="Sheet2")
        self.nums = self.wb.shape[0]
        self.header = [{
                           "User-Agent": "Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_6_8; en-us) AppleWebKit/534.50 (KHTML, like Gecko) Version/5.1 Safari/534.50"},
                       {
                           "User-Agent": "Mozilla/5.0 (Windows; U; Windows NT 6.1; en-us) AppleWebKit/534.50 (KHTML, like Gecko) Version/5.1 Safari/534.50"},
                       {"User-Agent": "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0;"},
                       {"User-Agent": "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0)"},
                       {"User-Agent": "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0)"},
                       {"User-Agent": "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"},
                       {"User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6; rv"},
                       {"User-Agent": "Mozilla/5.0 (Windows NT 6.1; rv"},
                       {
                           "User-Agent": "Opera/9.80 (Macintosh; Intel Mac OS X 10.6.8; U; en) Presto/2.8.131 Version/11.11"},
                       {"User-Agent": "Opera/9.80 (Windows NT 6.1; U; en) Presto/2.8.131 Version/11.11"},
                       {
                           "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_0) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11"},
                       {"User-Agent": "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Maxthon 2.0)"},
                       {"User-Agent": "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; TencentTraveler 4.0)"},
                       {"User-Agent": "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1)"},
                       {"User-Agent": "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; The World)"},
                       {
                           "User-Agent": "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Trident/4.0; SE 2.X MetaSr 1.0; SE 2.X MetaSr 1.0; .NET CLR 2.0.50727; SE 2.X MetaSr 1.0)"},
                       {"User-Agent": "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; 360SE)"},
                       {"User-Agent": "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Avant Browser)"},
                       {"User-Agent": "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1)"}, ]

    def read(self):  # 获取excel的数据
        for i in range(1, self.nums, 1):
            fileurl = self.wb.loc[i, 'fileurl']
            txt = etree.HTML(requests.get(fileurl, headers=random.choice(self.header), verify=False).text)
            cont = txt.xpath("//div[@class='article']")
            contet = cont[0].xpath('string(.)')
            self.wb.loc[i, 'content'] = contet
            print("培训第" + str(i) + "个")

            # self.save_mysql(self.wb.loc[i,'content'],fileurl)
            sleep(5)
        self.save_mysql()

    def open_datas(self):  # 打开数据库
        try:
            self.conn = pymysql.Connection(host=self.host, port=self.port, user=self.user, passwd=self.passwd,
                                           db=self.db, charset=self.charset)
            self.cursor = self.conn.cursor()
            print("打开数据库成功!")
            return self.conn, self.cursor
        except Exception as e:
            print("打开数据库失败>>>", e)

    def close_datas(self, conn, cursor):  # 关闭数据库
        try:
            cursor.close()
            conn.close()
            print("关闭数据成功!")
        except Exception as e:
            print("关闭数据库失败>>>", e)

    def save_mysql(self):  # 保存数据
        df(self.wb).to_excel(excel_writer=r'D:\wky\spider\training\traininger\training1.xlsx',
                             sheet_name='Sheet2', header=True, index=False, encoding='utf-8')

        # conn,cursor=self.open_datas()
        # 插入数据
        # try:
        #     if not cursor.execute('select srname from co_human_source where srname="{}"'.format(name)):
        #         cursor.execute('insert into co_human_source (keywordid,srname,fileurl) values("{}","{}","{}")'.format(id,name,url))
        #         conn.commit()
        #         print('录入到数据库成功!')
        #     else:
        #         pass
        # except Exception as e:
        #     print("录入到数据失败>>>",e)
        # 更改数据
        # try:
        #     cursor.execute('update co_human_source set content = "{}" where fileurl="{}"'.format(content,url))
        #     conn.commit()
        #     print("改变数据成功!")
        # except Exception as e:
        #     print("改变数据失败>>>",e)

        # self.close_datas(conn,cursor)

    def run(self):  # 运行
        self.read()


if __name__ == "__main__":
    hd = training_detail()
    hd.run()







